﻿<cfsilent>
	<cfscript>

		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		propAdvice = getProperty("serviceFactory").getBean("studentPropertyAdvice");
		
		teacherId = event.getArg('TchID');
		grade = event.getArg('Grade');
		
		sql = "SELECT tch_name FROM t_teacher WHERE tch_id = :teacherId ";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
		
   		queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
		
		rs_teacher = queryObj.execute( sql=sql ).getResult();
		
		sql = "SELECT 
					t_subject.sbj_name, t_class.grade, 
					t_class.cls_name, t_student.stu_id, 
					t_student.stu_name, t_student.stu_sex, 
					t_nation.nation_name, t_student.stu_idcard, 
					t_student.stu_birthday, t_political_affiliation.pa_shortname, 
					t_student_status.student_prop 
				FROM 
					t_student 
					INNER JOIN t_class ON t_class.cls_id = t_student.cls_id 
					INNER JOIN t_subject ON t_subject.sbj_id = t_class.sbj_id  
					INNER JOIN t_nation ON t_nation.nation_code = t_student.nation_code 
					INNER JOIN t_political_affiliation ON t_political_affiliation.pa_code = t_student.pa_code 
					INNER JOIN t_student_status ON t_student_status.stu_id = t_student.stu_id 
				WHERE 
					t_class.teacher_in_charge = :teacherId 
					AND 
					t_class.grade = :grade 
				ORDER BY 
					t_class.cls_name, t_student.stu_id ";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
   		queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
		queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
		
		rs_student = queryObj.execute( sql=sql ).getResult();
		
	
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat(rs_teacher.tch_name & "-" & grade & "级-管理学生名单", "utf-8");
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew( rs_teacher.tch_name & "-" & grade & "级-管理学生名单", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "专业,年级,班级,学号,姓名,性别,民族,身份证号,出生日期,政治面貌,在校,学籍,电子注册,辅修,双学位,出国留学,交换生");
		
		row = 1;
		
		for ( i=1; i LTE rs_student.recordCount; i++){
			
			row++;
			
			/* 构建学生信息 */
			
			/* 构建考生数据 */
			rowData = rs_student["sbj_name"][i]
						& "," & rs_student["grade"][i]
						& "," & rs_student["cls_name"][i]
						& "," & rs_student["stu_id"][i]
						& "," & rs_student["stu_name"][i];
			
			if ( rs_student["stu_sex"][i] eq "1" ) {
				rowData = rowData & ",男";
			}
			else {
				rowData = rowData & ",女";
			}
						
			rowData = rowData & "," & rs_student["nation_name"][i]
						& "," & rs_student["stu_idcard"][i]
						& "," & datetimeAdvice.formatDateString( rs_student["stu_birthday"][i], "YYYY/MM/DD" )
						& "," & rs_student["pa_shortname"][i] & ","
						& " , , , , , , ";
			
			/* 写入学生信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			/* 学生属性 */
			propAdvice.parseProperty( rs_student["student_prop"][i] );
			
			if ( propAdvice.getInSchool() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "是", row, 11);
			}
			else {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "否", row, 11);
			}
			
			if ( propAdvice.getRecognized() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "有", row, 12);
			}
			else {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "无", row, 12);
			}
			
			if ( propAdvice.getRegister() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "是", row, 13);
			}
			
			if ( propAdvice.getAssist() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "是", row, 14);
			}
			
			if ( propAdvice.getSecondary() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "是", row, 15);
			}
			
			if ( propAdvice.getOutCountry() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "是", row, 16);
			}
			
			if ( propAdvice.getExchanged() ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, "是", row, 17);
			}
			
			
						
		}
									
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);

	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>